# All base on centos 7
#!/bin/bash

# postgre集群的IP前缀
IP_CLUSTER="192.168.21"
# 需要远端访问的IP前缀
IP_REMOTES=(
  "192.168.10"
  "192.168.11"
  "192.168.20"
  "192.168.21"
)

echo "安装RPM包..."
rpm -ivh *.rpm
echo "---done"
sleep 1

echo "创建数据库目录并初始化数据..."
mkdir -p /opt/pgsql/14/data/
mkdir -p /opt/pgsql/14/log/
mkdir -p /opt/pgsql/14/pg_archive/
chown -R postgres:postgres /opt/pgsql/
su - postgres<<EOF
cd /usr/pgsql-14/bin/
./initdb -D /opt/pgsql/14/data/
exit
EOF
echo "---done"
sleep 1

echo "处理系统服务启动参数..."
sed -i 's/\/var\/lib\/pgsql\/14\/data\//\/opt\/pgsql\/14\/data\//g' /usr/lib/systemd/system/postgresql-14.service
systemctl daemon-reload
sleep 1

echo "启动服务..."
systemctl enable postgresql-14 && systemctl start postgresql-14
echo "---done"
sleep 1

echo "防火墙增加5432端口..."
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload
echo "---done"
sleep 1

echo "配置启动参数: 6G mem, 4 cores, HDD"
cat>/opt/pgsql/14/data/postgresql.conf<<EOF
max_connections = 200
shared_buffers = 1536MB
effective_cache_size = 4608MB
maintenance_work_mem = 384MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 3932kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
log_directory = '/opt/pgsql/14/log'  # directory where log files are written
EOF
echo "---done"
sleep 1
## 以下需要以postgresyoghurt在psql下运行
#
# postgres=# create role repl login replication encrypted password '$REPL_PASS';
#
#pg_dumpall > db.out
#psql -f db.out postgres

echo "导入数据库配置和数据"
su - postgres<<EOF
psql -f ./db.out postgres;
exit;
EOF
echo "---done"
sleep 1

echo "配置IP访问权限"
cat>/opt/pgsql/14/data/pg_hba.conf<<EOF
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    replication     repl            $IP_CLUSTER.0/24         md5
host    all             repl            $IP_CLUSTER.0/24         trust
EOF
for (( i=0;i<${#IP_REMOTES[@]};i++ )) do
echo "host    all             dbuser          ${IP_REMOTES[i]}.0/24         md5">>/opt/pgsql/14/data/pg_hba.conf
done;
echo "重启服务"
systemctl restart postgresql-14
echo "---done"
sleep 1
